package util;

import java.io.BufferedInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import util.bean.TicketOrderRep;
/**
 * ecxel格式上传
 * */
public class ExcelUtil {
	 /** 
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 
     *  全局参数title需要修改用于反射，得到需要的字段
     *  以及查询获取数据源需要修改
     * @param inputStream 
     *        文件流 
     * @param retMap
     * 		  返回 error:xxx
     */  
	public List<TicketOrderRep> readExcel(InputStream inputStream,Map<String,String> retMap) throws Exception{
		//直接用HSSFWorkbook和XSSFWorkbook所实现的接口Workbook
		Workbook wookbook = null;
		//调用下面的方法
		try {
			wookbook = createExcel(inputStream);
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//Sheet sheet = wookbook.getSheetAt(0);
		int numOfSheet = wookbook.getNumberOfSheets();
		List<TicketOrderRep> modelList = new ArrayList<TicketOrderRep>();
        //依次解析每一个Sheet
        for (int i = 0; i < numOfSheet; ++i) {
            Sheet sheet = wookbook.getSheetAt(i);
         //   List<TicketOrderRep> modelList = new ArrayList<>();
            Map<String,String> rMap = parseSheet(sheet, modelList);
            if(rMap!=null){
            	retMap.put("error", rMap.get("error"));
            	return null;
            }
        }
		return modelList;
	}
	//保存需要调用的ApnModel中的方法
     private List<Method> mUsedMethod;
     //保存需要保存的列
     public static String[] title = {"goodsname","spotgoodsname","contact","credentialnumber","phone","orderno","price","balanceprice","num","totalmoney","totalbalancemoney","payamount","orderstate","paystate","paymentmethod","checkstate","checknum","checkticket","numberofrefund","ordertime","starttime","endtime","playtime","checkpoint","checktime","thirdpartycoding","area","distributor","supplier","servicecode","orderpeople","remark","distributorremark","address","birthday","sex","travelagency","abnormalnumrefunds","abnormalcashrefunds","costprice"};
	 private  Map<String,String> parseSheet(Sheet sheet, List<TicketOrderRep> apnModelList) throws Exception {
	        Row row;
	        Map<String,String> retMap = new HashMap<String,String>();
	        int count = 0;

	        //利用迭代器，取出每一个Row
	        Iterator<Row> iterator = sheet.iterator();
	        while(iterator.hasNext()) {
	            row = iterator.next();

	            //由于第一行是标题，因此这里单独处理
	            if (count == 0) {
	                mUsedMethod = new ArrayList<Method>();
	                int columnNum=row.getPhysicalNumberOfCells();
	                if(columnNum!=40){
	                	throw new Exception("文件格式错误，列数超过40");
	                }
	                parseRowAndFindMethod(row);
	            } else {
	                //其它行都在这里处理
	            	retMap = parseRowAndFillData(row, apnModelList);
	            	if(retMap!=null){
	            		return retMap;
	            	}
	            }

	            ++count;
	        }
			return null;
	    }
	 
	 private void parseRowAndFindMethod(Row row) {
	        //利用parseRow处理每一行，得到每个cell中的String
	        List<String> rst = parseRow(row,true);

	        String methodName;
	        try {
	            //根据String得到需要调用的ApnModel中的方法
	            //由于自己在ApnModel中定义的方法均是类似setMcc、setMnc等
	            //因此才在一开始，将标题栏中每一项大写
	            for (String str : rst) {
	                methodName = "set" + str;
	                if(str.contains("time")||str.contains("Birthday")){
	                	 //反射拿到method
		                mUsedMethod.add(
		                        TicketOrderRep.class.getDeclaredMethod(methodName, Timestamp.class));
	                }else if(str.contains("id")){
	                	 //反射拿到method
		                mUsedMethod.add(
		                        TicketOrderRep.class.getDeclaredMethod(methodName, Integer.class));
	                }else{
	                	 //反射拿到method
		                mUsedMethod.add(
		                        TicketOrderRep.class.getDeclaredMethod(methodName, String.class));
	                }
	               
	            }
	        } catch (NoSuchMethodException e) {
	            System.out.println(e.toString());
	        }

	    }

	//开始解析具体的数据
	    private Map<String,String> parseRowAndFillData(Row row, List<TicketOrderRep> apnModelList) {
	        //同样利用parseRow得到具体每一行的数据
	        List<String> rst = parseRow(row,false);
	        Map<String,String> retMap = new HashMap<String,String>();
	        TicketOrderRep apnModel = new TicketOrderRep();

	        //这里主要debug一下，避免由于Excel的格式可能不太对
	        //使得每一行的数据解析地不太对
	        if (mUsedMethod.size() < rst.size()) {
	            System.out.println("WTF, size not right");
	        } else {
	            //利用反射，将数据填充到具体的ApnModel
	            try {
	                for (int i = 0; i < rst.size(); ++i) {
	                	 String str = mUsedMethod.get(i).getName();
	                	 if(str.contains("Orderno")&&(rst.get(i).length()<=0||rst.get(i)==null)){             		 
	                		 return null;
	                	 }else if(str.contains("Orderno")){
	                		 TicketOrderRep arg0 = new TicketOrderRep();
	                		 arg0.setOrderno(rst.get(i).trim());
	                		 /**替换地方**/
	                		 TicketOrderRep retBean = null;//DaoFactory.create(TicketOrderRep.class).selectOne(arg0 );
	                		 if(retBean!=null&&rst.get(i).trim().equalsIgnoreCase(retBean.getOrderno())){
	                			 //订单号是否重复
		                		 retMap.put("error", "订单号重复！");
		                		 return retMap;
	                		 }                		 
	                	 }
	                	 if(str.contains("time")||str.contains("Birthday")){
	                		 if(rst.get(i)==null||rst.get(i).length()<=0){	                			 
		                		//  mUsedMethod.get(i).invoke(apnModel, null);
	                		 }else{
	                			 @SuppressWarnings("deprecation")
								long tt = new Date(rst.get(i).replace("-", "/")).getTime();
		                		 mUsedMethod.get(i).invoke(apnModel, new Timestamp(tt));
	                		 }                		 
	 	                }else{
	 	                	mUsedMethod.get(i).invoke(apnModel, rst.get(i));
	 	                }
	                    
	                }

	                //保存到输出结果中
	                apnModelList.add(apnModel);
	            } catch (Exception e) {
	                e.printStackTrace();
	            }
	        }
			return null;
	    }

	    //这里是解析每一行的代码
	    private List<String> parseRow(Row row,boolean isDefine) {
	    	List<String> rst = new ArrayList<String>();
	    	if(isDefine){
	    		for(String temp : title){
	    			rst.add(toUpperCase4Index(temp));
	    		}
	    	}else{
		        Cell cell;
		        //利用迭代器得到每一个cell
		        Iterator<Cell> iterator = row.iterator();
		        while (iterator.hasNext()) {
		            cell = iterator.next();

		            //定义每一个cell的数据类型
		            cell.setCellType(Cell.CELL_TYPE_STRING);
		            //取出cell中的value
		            rst.add(cell.getStringCellValue());
		        }
	    	}
	        

	        return rst;
	    }

	private static Workbook createExcel(InputStream inp) throws IOException, InvalidFormatException {
		if (!inp.markSupported()) {
	        inp = new PushbackInputStream(inp, 8);
	    }
	    if (POIFSFileSystem.hasPOIFSHeader(inp)) {
	        return new HSSFWorkbook(inp);
	    }
	    if (POIXMLDocument.hasOOXMLHeader(inp)) {
	        return new XSSFWorkbook(OPCPackage.open(inp));
	    }
	    throw new IllegalArgumentException("你的excel版本目前poi解析不了");/**/
		//return new XSSFWorkbook(OPCPackage.open(inp));
	}
	
	/** 
	 * 首字母大写 
	 *  
	 * @param string 
	 * @return 
	 */  
	public static String toUpperCase4Index(String string) {  
	    char[] methodName = string.toCharArray();  
	    methodName[0] = toUpperCase(methodName[0]);  
	    return String.valueOf(methodName);  
	}  
	  
	/** 
	 * 字符转成大写 
	 *  
	 * @param chars 
	 * @return 
	 */  
	public static char toUpperCase(char chars) {  
	    if (97 <= chars && chars <= 122) {  
	        chars ^= 32;  
	    }  
	    return chars;  
	}  
	public static void main(String[] args) {
		System.out.println(new Date("2018/06/02 08:55:46").getTime());
	}
	
	/**
	 * 文件保存
	 * */
	public void saveToFile(String fileName, InputStream in) throws IOException {   
        FileOutputStream fos = null;      
        BufferedInputStream bis = null;      
//          HttpURLConnection httpUrl = null;      
//          URL url = null;      
        int BUFFER_SIZE = 1024;   
        byte[] buf = new byte[BUFFER_SIZE];      
        int size = 0;      
//                
//          //建立链接      
//          url = new URL(destUrl);      
//          httpUrl = (HttpURLConnection) url.openConnection();      
//          //连接指定的资源      
//          httpUrl.connect();      
//          //获取网络输入流      
        bis = new BufferedInputStream(in);      
//          //建立文件      
        System.out.println("开始保存"+fileName);
        fos = new FileOutputStream(fileName);      
//           
//          //保存文件      
        while ( (size = bis.read(buf)) != -1)       
          fos.write(buf, 0, size);      
//                
        fos.close();      
        bis.close();      
//          httpUrl.disconnect();      
      }   
}
